import pandas as pd
from pandas import DataFrame

from biz.pandas.io.BasePa import BasePa

class demo(BasePa):
    '''
    学业警告 院系人数
    '''

    def getDF(self, conn) -> DataFrame:
        df = pd.read_sql('''
        SELECT xt.xn,xt.xq,xdx.`name` '学业预警',xs.* 
        FROM (select  * from `XH_T_XYJG` where jglx=1 and xn='2022-2023' and xq=2) xt 
        LEFT JOIN XH_T_DM_XYJGLX xdx on xt.jglx=xdx.id
        LEFT JOIN(select xs.xh,xs.yxm '院系码',xs.xqm,xs.pycc,T_DW.dwmc '院系名称',T_XQ.xqmc from T_XSJBXX xs 
        left join T_DW on T_DW.dwm=xs.yxm 
        left join T_XQ on T_XQ.xqdm=xs.xqm) xs on xs.xh=xt.xh
        ''', conn)
        df=df.groupby(['院系码','院系名称'])['学业预警'].count().reset_index()
        return df
